import os
import django
import random

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'backend.settings')
django.setup()

# 这个必须在 django 建立环境之后
from authorization.models import User
from apis.models import App

# ----------- 数据库函数 ------------

def ranstr(length):
    CHS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklnmopqrstuvwxyz0123456789'
    slat = ''
    for i in range(length):
        slat += random.choice(CHS) # 随机取出字段
    return slat


# 添加一个用户
def add_one():
    # 第一种添加方法
    user = User(open_id='test_open_id_1', nickname="test_nickname_1")
    user.save()
    # 第二种添加方法
    # User.objects.create(open_id='test_open_id_2', nickname="test_nickname_2")
    print('Add Success')


# 添加一条新数据
def add_one_app():
    name = repr('图片备份')
    app = App(
        category='life',
        application='backup-image',
        name='\\xE5\\x9B\\xBE\\xE7\\x89\\x87',
        publish_date='2018-10-02',
        url='/service/image',
        desc='this is a backup image app'
    )
    app.save()
    print('Add Success')


# 批量插入数据
def add_batch():
    new_user_list = []

    for i in range(10):
        open_id = ranstr(32)
        nickname = ranstr(10)
        user = User(open_id=open_id, nickname=nickname)
        new_user_list.append(user)

    User.objects.bulk_create(new_user_list) # 批量添加数据
    print('Add Success')


# 查询单个数据
# objects.get 方法如果查询出来的数据不唯一，就会报错
def get_one():
    user = User.objects.get(open_id='test_open_id_1')
    print(user)


# 数据过滤
# 数据过滤方式 属性名__关键字
# open_id__contains='test_'，包含 test_
# open_id__startswith='test'，以 test 开头
def get_filter():
    users = User.objects.filter(open_id__contains='test_')
    print(users)


# 数据排序
def get_order():
    users = User.objects.order_by('open_id')
    print(users)


# 连锁查询
def get_chain():
    users = User.objects.filter(open_id__contains='test_').order_by('open_id')
    print(users)


# 修改一条数据
def modify_one():
    user = User.objects.get(open_id='test_open_id')
    user.nickname = 'modify_username'
    user.save()


# 修改多条数据
def modify_batch():
    User.objects.filter(open_id__contains='test_').update(nickname='modify_batch_username')


# 删除一条数据
def delete_one():
    User.objects.get(open_id='test_open_id').delete()


# 批量删除数据
def delete_patch():
    User.objects.filter(open_id__contains='test_').delete()


# 全部删除
def delete_all():
    User.objects.all().delete()


# ----------- 字符串函数 ------------

# 字符串拼接：Concat
from django.db.models import Value
from django.db.models.functions import Concat
def concat_function():
    user = User.objects.filter(open_id='test_open_id_1').annotate(
        srceen_name = Concat( Value('open_id='), 'open_id', Value(','), Value('nickname='), 'nickname' )
    )[0]
    print(user.srceen_name)


# 字符串长度：Length
from django.db.models.functions import Length
def length_function():
    user = User.objects.filter(open_id='test_open_id_1').annotate( open_id_length=Length('open_id') )[0]
    print(user.open_id_length)


# 大小写函数：Upper/Lower
# Upper：全部变成大写
# Upper：全部变成大写
from django.db.models.functions import Upper, Lower
def case_function():
    user = User.objects.filter(open_id='test_open_id_1').annotate(
        upper_open_id=Upper('open_id'),
        lower_open_id=Lower('open_id')
    )[0]
    print(user.upper_open_id)
    print(user.lower_open_id)


# ----------- 日期函数 ------------

# 当前时间：Now
from django.db.models.functions import Now
def now_funciton():
    # 当前日期之前发布的所有应用
    apps = App.objects.filter(publish_date__lte=Now())
    print(apps)


# 时间截断函数：Trunc
from django.db.models import Count
from django.db.models.functions import Trunc
def trunc_function():
    # 打印每一天发布的应用数量
    app_per_day = App.objects.annotate( publish_date=Trunc('publish_date', 'day') ).values('publish_day').annotate( publish_num=Count('appid') )
    print(app_per_day)


if __name__ == '__main__':
    add_one_app()
